package com.joinway.framework.data.jdbc.repository;

import groovy.lang.GString;
import groovy.sql.Sql;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.sql.DataSource;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.joinway.framework.bean.domain.Domain;
import com.joinway.framework.bean.domain.annonation.PersistFormat;
import com.joinway.framework.bean.domain.constant.SqlConstants;
import com.joinway.framework.bean.domain.type.PersistCase;
import com.joinway.framework.data.jdbc.bean.ColumnMapping;
import com.joinway.framework.data.jdbc.bean.DomainClassMetaData;
import com.joinway.framework.data.jdbc.bean.DomainFieldMetaData;
import com.joinway.framework.data.jdbc.bean.IdField;
import com.joinway.framework.data.jdbc.repository.utils.JdbcDataFactory;
import com.joinway.framework.extension.groovy.DBConverter;
import com.joinway.framework.extension.groovy.DomainUtils;
import com.joinway.framework.extension.groovy.GroovyUtils;
import com.joinway.framework.extension.utils.DataUtils;

/**
 * 基于Groovy {@link groovy.sql.Sql} 的数据库访问通用类。可以将sql语句转化为java对象，或者转化为动态Groovy对象。<br>
 * 支持动态方法查询语句，如findByNameAndPassword动态方法会被转化为select * from xxx where name = ? and password = ?
 * 
 * @author liliang
 *
 */
public class TableRepository extends AbstractTableRepository {

	private final static Logger log = LoggerFactory.getLogger(TableRepository.class);
	
	/**
	 * read/write datasource
	 */
	protected DataSource dataSource;
	
	protected Connection connection;
	
	public TableRepository(DataSource dataSource){
		super(new Sql(dataSource));
		this.dataSource = dataSource;
	}
	
	public TableRepository(Connection connection){
		super(new Sql(connection));
		this.connection = connection;
	}

//	public TableRepository(Sql sqlClient){
//		super(sqlClient);
//	}
	
	@Override
	protected Object createDomain(Class<?> domainClass, List<ColumnMapping> columnNames, Object row) throws Exception {
		Object domain = domainClass != null ? domainClass.newInstance() : new Domain();
		
		for(ColumnMapping it : columnNames){
			String originalColumnName = it.getOriginalColumnName();
			String columnName = it.getColumnName();
			
			Object value = GroovyUtils.getProperty(row, columnName);
			boolean alias = !originalColumnName.equals(columnName);
			
			if(domainClass != null){
				Field field = getDomainField(columnName, domainClass, alias);
				
				if(field != null){
					String fieldName = field.getName();
					
					Object domainValue = DomainUtils.convertValue(field.getType(), value);
					
					GroovyUtils.setMethod(domain, fieldName, domainValue);
//					domain."${propName}"(domainValue);
				}
			}else{
				String fieldName;
				if(alias){
					fieldName = columnName;
				}else{
					fieldName = DomainUtils.convertToDomainFieldName(columnName);
				}
				
				GroovyUtils.addProperty(domain, fieldName, value);
//				domain.metaClass."$fieldName" = value;
			}
		}
		
//		if(domain instanceof AbstractDomain){
//			log.info("domain {}", ((AbstractDomain)domain).getId());
//		}
		return domain;
	}
	
	/**
	 * 	
	 * @param domainClass
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public<T> T findById(Class<T> domainClass, Object id) throws Exception{
		return findById(domainClass, id, null);
	}
	
	/**
	 * 
	 * @param domainClass
	 * @param id
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public<T> T findById(Class<T> domainClass, Object id, String table) throws Exception{
		DomainClassMetaData meta = JdbcDataFactory.createDomainClassMetaData(domainClass);
		Optional<DomainFieldMetaData> optional = meta.getDomainFieldMetaDatas().stream().filter(m -> m.isIdKey()).findFirst();
		
		T domain = null;
		
		if(optional.isPresent()){
			String tableName = StringUtils.isNotEmpty(table) ? table : meta.getTableName();
			String sql = "select * from " + tableName + " where " + optional.get().getColumnName() + " = ?";
			List<T> domains = find(domainClass, sql, id);
			domain = super.getFirst(domains);
		}
		
		return domain;
	}

	/**
	 * 
	 * @param domainClass
	 * @param ids
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> findByIds(Class<T> domainClass, Object...ids) throws Exception{
		List<T> domains = null;
		
		if(ArrayUtils.isNotEmpty(ids)){
			DomainClassMetaData meta = JdbcDataFactory.createDomainClassMetaData(domainClass);
			Optional<DomainFieldMetaData> optional = meta.getDomainFieldMetaDatas().stream().filter(m -> m.isIdKey()).findFirst();
			
			if(optional.isPresent()){
				List<String> holders = new ArrayList<>();
				Arrays.asList(ids).stream().forEach(i -> holders.add("?"));
				
				String sql = "select * from " + meta.getTableName() + " where " + optional.get().getColumnName() + " in (" + StringUtils.join(holders, ",") + ")";
				domains = find(domainClass, sql, ids);
			}
		}
		
		return domains;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<?> find(String sql) throws Exception{
		logSql(sql);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, getEachRowClosure(records));
		
		return records;
	}

	/**
	 * 
	 * @param domainClass
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(Class<T> domainClass, String sql) throws Exception{
		logSql(sql);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, getEachRowClosure(records, domainClass));
		
		return records;
	}

	/**
	 * 
	 * @param params jdbc命名参数
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<?> find(Map<String, Object> params, String sql) throws Exception{
		logSql(sql);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, params, getEachRowClosure(records));
		
		return records;
	}
	
	/**
	 * @param sql
	 * @param params 
	 * @return
	 * @throws Exception
	 */
	public List<?> find(String sql, Object...params) throws Exception{
		logSql(sql, params);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, toList(params), getEachRowClosure(records));
		
		return records;
	}

	/**
	 * 
	 * @param sql
	 * @param page 页号，从1开始
	 * @param rows 每页行数
	 * @return
	 * @throws Exception
	 */
	public List<?> find(String sql, int page, int rows) throws Exception{
		logSql(sql);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, (page - 1) * rows + 1, rows, getEachRowClosure(records));
		
		return records;
	}
	
	/**
	 * 
	 * @param domainClass
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(Class<T> domainClass, String sql, Object...params) throws Exception{
		logSql(sql, params);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, toList(params), getEachRowClosure(records, domainClass));
		
		return records;
	}
	
	/**
	 * 
	 * @param sql
	 * @param params jdbc命名参数
	 * @param page 页号，从1开始
	 * @param rows 每页行数
	 * @return
	 * @throws Exception
	 */
	public List<?> find(String sql, Map<String, Object> params, int page, int rows) throws Exception{
		logSql(sql, params);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, params, (page - 1) * rows + 1, rows, getEachRowClosure(records));
		
		return records;
	}
	
	/**
	 * 
	 * @param sql
	 * @param page 页号，从1开始
	 * @param rows 每页行数
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<?> find(String sql, int page, int rows, Object...params) throws Exception{
		logSql(sql, params);
		
		List<?> records = new LinkedList<>();
		getSqlClient().eachRow(sql, toList(params), (page - 1) * rows + 1, rows, getEachRowClosure(records));
		
		return records;
	}
	
	/**
	 * 
	 * @param sql
	 * @param domainClass
	 * @param page 页号，从1开始
	 * @param rows 每页行数
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(String sql, Class<T> domainClass, int page, int rows) throws Exception{
		logSql(sql);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, (page - 1) * rows + 1, rows, getEachRowClosure(records, domainClass));
		
		return records;
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @param domainClass
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(String sql, Map<String, Object> params, Class<T> domainClass) throws Exception{
		logSql(sql);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, params, getEachRowClosure(records, domainClass));
		
		return records;
	}

	/**
	 * 
	 * @param sql
	 * @param domainClass
	 * @param page
	 * @param rows
	 * @param params 
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(String sql, Class<T> domainClass, int page, int rows, Object...params) throws Exception{
		logSql(sql, params);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, toList(params), (page - 1) * rows + 1, rows, getEachRowClosure(records, domainClass));
		
		return records;
	}
	
	/**
	 * 
	 * @param sql
	 * @param params 
	 * @param domainClass
	 * @param page
	 * @param rows
	 * @return
	 * @throws Exception
	 */
	public<T> List<T> find(String sql, Map<String, Object> params, Class<T> domainClass, int page, int rows) throws Exception{
		logSql(sql, params);
		
		List<T> records = new LinkedList<>();
		getSqlClient().eachRow(sql, params, (page - 1) * rows + 1, rows, getEachRowClosure(records, domainClass));
		
		return records;
	}

	/**
	 * 
	 * @param id 主键
	 * @param domainClass
	 * @return
	 */
	public<T> T findOne(Object id, Class<T> domainClass) throws Exception {
		DomainClassMetaData meta = JdbcDataFactory.createDomainClassMetaData(domainClass);
		String tableName = meta.getTableName();
		
		IdField field = getIdField(domainClass.newInstance());
		
		List<T> domains = find(domainClass, "select * from " + tableName + " where " + field.getColumnName() + " = ?", id);
		
		return super.getFirst(domains);
	}
	
	/**
	 * 
	 * @param domainClass
	 * @return
	 */
	public<T> List<T> findAll(Class<T> domainClass) throws Exception {
		DomainClassMetaData meta = JdbcDataFactory.createDomainClassMetaData(domainClass);
		return find(domainClass, "select * from " + meta.getTableName());
	}
	
	public<T> List<T> findAll(Class<T> domainClass, int page, int rows) throws Exception {
		DomainClassMetaData meta = JdbcDataFactory.createDomainClassMetaData(domainClass);
		return find("select * from " + meta.getTableName(), domainClass, page, rows);
	}
	
	/**
	 * 
	 * @param sql
	 * @return 实际更新行数
	 * @throws Exception
	 */
	public int update(GString sql) throws Exception {
		logSql(sql);
		return getSqlClient().executeUpdate(sql);
	}
	
	/**
	 * 
	 * @param sql
	 * @return 实际更新行数
	 * @throws Exception
	 */
	public int update(String sql) throws Exception {
		logSql(sql);
		return getSqlClient().executeUpdate(sql);
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return 实际更新行数
	 * @throws Exception
	 */
	public int update(String sql, Object...params) throws Exception {
//		logSql(sql, params);
		return executeUpdate(sql, toList(params));
	}
	
	/**
	 * 
	 * @param params
	 * @param sql
	 * @return 实际更新行数
	 * @throws Exception
	 */
	public int update(Map<String, Object> params, String sql) throws Exception {
//		logSql(sql, params);
		return executeUpdate(sql, params);
	}
	
	/**
	 * 
	 * @param sql
	 * @return 自增主键
	 * @throws Exception
	 */
	public List<List<Object>> insert(GString sql) throws Exception {
//		logSql(sql);
		return executeInsert(sql.toString());
	}
	
	/**
	 * 
	 * @param sql
	 * @return 自增主键
	 * @throws Exception
	 */
	public List<List<Object>> insert(String sql) throws Exception {
//		logSql(sql);
		return executeInsert(sql);
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return 自增主键
	 * @throws Exception
	 */
	public List<List<Object>> insert(String sql, Object...params) throws Exception {
//		logSql(sql, params);
		return executeInsert(sql, toList(params));
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return 自增主键
	 * @throws Exception
	 */
	public List<List<Object>> insert(Map<String, Object> params, String sql) throws Exception {
//		logSql(sql, params);
		return executeInsert(sql, params);
	}
	
	/**
	 * 更新或保存域对象
	 * @param domain 域对象
	 * @return 更新或保存后的域对象
	 * @throws Exception
	 */
	public<T> T save(T domain) throws Exception{
		return save(domain, null);
	}
	
	/**
	 * 更新或保存域对象
	 * @param domain 域对象
	 * @param table 表名
	 * @return 更新或保存后的域对象
	 * @throws Exception
	 */
	public<T> T save(T domain, String table) throws Exception{
		DomainClassMetaData domainClassMetaData = JdbcDataFactory.createDomainClassMetaData(domain.getClass());
		
		String tableName = StringUtils.isNotBlank(table) ? table : domainClassMetaData.getTableName();

		// key - column name; value - targe value to be saved
		Map<String, Object> columnValueMap = new LinkedHashMap<>();
		
		// key - column name; value target field
		Map<String, Field> dateCreatedColumnFields = new LinkedHashMap<>();

		String idColumnName = null, idFieldName = null;
		Object idFieldValue = null;
		
		for(DomainFieldMetaData it : domainClassMetaData.getDomainFieldMetaDatas()){
			DomainFieldMetaData meta = it;
			Field field = meta.getField();
			
			String fieldName = field.getName();
			Object originalFieldValue = GroovyUtils.getProperty(domain, fieldName);
			
			Object value;
			
			if(meta.isLastUpdated()){
				value = new Date();
				GroovyUtils.setProperty(domain, fieldName, value);
				
			}else{
				value = DomainUtils.convertValue(field.getType(), originalFieldValue);
				
				if(value != null && (value instanceof Character)){
					// work around of char to db char(1) type issue
					// see http://bugs.mysql.com/bug.php?id=59456
					value = String.valueOf(value);
				}
			}
			
			if(meta.isDateCreated()){
				dateCreatedColumnFields.put(meta.getColumnName(), field);
			}
			
			if(!meta.isIdKey()){
				value = formatFieldValue(value, field);
				columnValueMap.put(meta.getColumnName(), value);
			}else{
				idColumnName = meta.getColumnName();
				idFieldName = field.getName();
				idFieldValue = DomainUtils.convertValue(field.getType(), GroovyUtils.getProperty(domain, fieldName));
			}
		}
		
		List<String> setClauses = new LinkedList<>();
		columnValueMap.keySet().stream().forEach(k -> setClauses.add(k + " = ?"));
		
		String columns = StringUtils.join(setClauses, ",");
		String sql = "update " + tableName + " set " + columns + " where " + idColumnName + " = ?";
		
		List<Object> params = new ArrayList<>(columnValueMap.values());
		params.add(idFieldValue);
		
		log.debug("update domain class {} by sql -> {}", domain.getClass().getName(), sql);
		log.debug("sql params -> {}", params);
		
		int count = getSqlClient().executeUpdate(sql, params);
		
		log.debug("updated rows {}", count);
		
		if(count == 0){
			log.info("no row was updated, insert row instead");
			// nothing was updated
			columns = StringUtils.join(columnValueMap.keySet(), ",");
			int valueCount = columnValueMap.values().size();
			
			List<String> valueElements = new LinkedList<>();
			for(int i = 0; i < valueCount; i++){
				valueElements.add("?");
			}
			
			Map<String, Object> insertMap = new LinkedHashMap<>(columnValueMap);
			Iterator<String> kk = insertMap.keySet().iterator();
			while(kk.hasNext()){
				String it = kk.next();
				if(dateCreatedColumnFields.containsKey(it)){
					Date today = new Date();
					insertMap.put(it, today);
					
					Field field = dateCreatedColumnFields.get(it);
					String fieldName = field.getName();
					GroovyUtils.setProperty(domain, fieldName, today);
				}
			}
		
			params = new ArrayList<>(insertMap.values());
			
			sql = "insert into " + tableName + " (" + columns + ") values (" + StringUtils.join(valueElements, ",") + ")";
			
			log.info("execute sql -> {}", sql);
			log.debug("sql params -> {}", params);
	
			List<List<Object>> results = getSqlClient().executeInsert(sql, params);
			
			if(CollectionUtils.isNotEmpty(results) && CollectionUtils.isNotEmpty(results.get(0))){
				Object d = results.get(0).get(0);
				GroovyUtils.setProperty(domain, idFieldName, d);
			}
//			def (e) = sqlClient.executeInsert(sql, params);
//			Object d = e?.get(0);
//			domain."$idFieldName" = d;
		}
		
		return domain;
	}
	
	public Object persist(Object domain) throws Exception {
		return persist(null, domain, SqlConstants.Key.Id);
	}
	
	public Object persist(String table, Object domain) throws Exception {
		return persist(table, domain, SqlConstants.Key.Id);
	}
	
	public Object persist(Object domain, String idFieldName) throws Exception {
		return persist(null, domain, idFieldName);
	}
	
	/**
	 * 更新或保存Groovy动态域对象
	 * @param table 表名
	 * @param domain Groovy动态域对象
	 * @param idFieldName JavaBean格式的主键字段
	 * @return 如果是更新，返回实际更新行数；如果是插入返回自增主键
	 * @throws Exception
	 */
	public Object persist(String table, Object domain, String idFieldName) throws Exception {
		Object ret = null;
		
		String idColumnName = DomainUtils.convertToDBName(idFieldName);
		if(StringUtils.isBlank(table)){
			table = DomainUtils.getTableName(domain.getClass());
		}
		
		Map<String, Object> properties = GroovyUtils.getProperties(domain);
		Optional<String> optional = properties.keySet().stream().filter(k -> k.equals(idFieldName)).findFirst();
		
		List<?> source = null;
		if(optional.isPresent()){
			source = find("select * from " + table + " where " + idColumnName + " = ?", GroovyUtils.getProperty(domain, idFieldName));
		}
		
		Map<String, Object> params = new LinkedHashMap<>();
		String sql;
		
		if(CollectionUtils.isNotEmpty(source)){
			// update
			sql = "update " + table + " set ";
			
			Iterator<String> keys = properties.keySet().iterator();
			while(keys.hasNext()){
				String fieldName = keys.next();
				Object value = properties.get(fieldName);
				
				if(!AST_FIELD_NAMES.contains(fieldName)){
					String columnName = DomainUtils.convertToDBName(fieldName);
					
					if(!SqlConstants.RESERVED_VALUES.contains(value)){
						params.put(fieldName, value);
						sql += columnName + " = :" + fieldName + ",";
					}else{
						sql += columnName + " = " + value + ",";
					}
				}
				
			}
			
			sql = sql.substring(0, sql.length() - 1);
			
			sql += " where " + idColumnName + "= :" + idFieldName;
			
			logSql(sql, params);
			
			int count = getSqlClient().executeUpdate(params, sql);
			ret = count;
			
		}else{
			// insert
			sql = "insert into " + table;
			
			String columns = "";
			String values = "";
			
			Iterator<String> keys = properties.keySet().iterator();
			while(keys.hasNext()){
				String fieldName = keys.next();
				Object value = properties.get(fieldName);
				
				if(GroovyUtils.isTrue(value)){
					if(!AST_FIELD_NAMES.contains(fieldName)){
						String columnName = DomainUtils.convertToDBName(fieldName);
						columns += columnName + ",";
						
						if(!SqlConstants.RESERVED_VALUES.contains(value)){
							params.put(fieldName, value);
							values += ":" + fieldName + ",";
						}else{
							values += value;
						}
					}
				}
			}
			
			columns = columns.substring(0, columns.length() - 1);
			values = values.substring(0, values.length() - 1);
			
			sql += "(" + columns + ") values (" + values + ")";
			logSql(sql, params);
			
			List<List<Object>> results = getSqlClient().executeInsert(params, sql);
			if(CollectionUtils.isNotEmpty(results) && CollectionUtils.isNotEmpty(results.get(0))){
				ret = results.get(0).get(0);
			}
		}
		
		return ret;
	}
	
	/**
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int delete(GString sql) throws Exception {
		logSql(sql);
		return executeDelete(sql.toString());
	}
	
	/**
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int delete(String sql) throws Exception {
		logSql(sql);
		return executeDelete(sql);
	}

	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public int delete(String sql, Object...params) throws Exception {
		logSql(sql, params);
		return executeDelete(sql, toList(params));
	}
	
	/**
	 * 
	 * @param id
	 * @param domainClass
	 * @return 
	 * @throws Exception
	 */
	public int delete(Object id, Class<?> domainClass) throws Exception {
		DomainClassMetaData domainClassMetaData = JdbcDataFactory.createDomainClassMetaData(domainClass);
		
		String tableName = domainClassMetaData.getTableName();
		IdField field = getIdField(domainClass.newInstance());
//		def (idFieldName, idFieldValue, idColumnName) = getIdFieldConfig(domainClass.newInstance())
		
		String sql = "delete from " + tableName + " where " + field.getColumnName() + " = ?";
		log.debug("execute delete sql -> {}", sql);
		
		return executeDelete(sql, id);
	}

	/**
	 * 删除表中所有数据
	 * @param domainClass
	 * @return
	 * @throws Exception
	 */
	public boolean deleteAll(Class<?> domainClass) throws Exception {
		DomainClassMetaData domainClassMetaData = JdbcDataFactory.createDomainClassMetaData(domainClass);
		
		String tableName = domainClassMetaData.getTableName();
		
		return deleteAll(tableName);
	}	
	
	/**
	 * 删除表中所有数据
	 * @param tableName 表名
	 * @return
	 * @throws Exception
	 */
	public boolean deleteAll(String tableName) throws Exception {
		String sql = "delete from " + tableName;
		logSql(sql);
		
		return getSqlClient().execute(sql);
	}
	
	
	protected int executeUpdate(String sql) throws Exception {
		logSql(sql);
		return getSqlClient().executeUpdate(sql);
	}
	
	protected int executeUpdate(String sql, Object...params) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeUpdate(sql, toList(params));
	}
	
	protected int executeUpdate(Map<String, Object> params, String sql) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeUpdate(params, sql);
	}

//	/**
//	 * 
//	 * @param sql
//	 * @param params
//	 * @return
//	 * @throws Exception
//	 */
//	protected int executeUpdate(String sql, Object params) throws Exception {
//		params = normalizeParams(params);
//		
//		log.info("execute sql -> {}", sql);
//		log.debug("sql params -> {}", params);
//		
//		int count = 0;
//		
//		if(params != null){
//			if(params instanceof List){
//				count = sqlClient.executeUpdate(sql, (List)params);
//			}else if(params instanceof Map){
//				count = sqlClient.executeUpdate((Map)params, sql);
//			}
//		}else{
//			count = sqlClient.executeUpdate(sql);
//		}
//		
//		return count;
//	}
	
	protected List<List<Object>> executeInsert(String sql) throws Exception {
		logSql(sql);
		return getSqlClient().executeInsert(sql);
	}

	protected List<List<Object>> executeInsert(String sql, Object...params) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeInsert(sql, toList(params));
	}

	protected List<List<Object>> executeInsert(Map<String, Object> params, String sql) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeInsert(params, sql);
	}
	
//	/**
//	 * 
//	 * @param sql
//	 * @param params
//	 * @return
//	 * @throws Exception
//	 */
//	protected List<List<Object>> executeInsert(String sql, Object params) throws Exception {
//		params = normalizeParams(params);
//		
//		List<List<Object>> results = null;
//		
//		if(params != null){
//			if(params instanceof List){
//				results = sqlClient.executeInsert(sql, (List)params);
//			}else if(params instanceof Map){
//				results = sqlClient.executeInsert((Map)params, sql);
//			}
//		}else{
//			results = sqlClient.executeInsert(sql);
//		}
//		
//		return results;
//	}
	
	protected int executeDelete(String sql) throws Exception {
		logSql(sql);
		return getSqlClient().executeUpdate(sql);
	}
	
	protected int executeDelete(String sql, Object...params) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeUpdate(sql, toList(params));
	}
	
	protected int executeDelete(Map<String, Object> params, String sql) throws Exception {
		logSql(sql, params);
		return getSqlClient().executeUpdate(params, sql);
	}

//	/**
//	 * 
//	 * @param sql
//	 * @param params
//	 * @return
//	 * @throws Exception
//	 */
//	protected int executeDelete(String sql, def params = null) throws Exception {
//		params = normalizeParams(params)
//		
//		log.info("execute sql -> {}", sql)
//		log.debug("sql params -> {}", params)
//			
//		if(params){
//			if(params instanceof List){
////				sqlClient.execute(sql, params)
//				sqlClient.executeUpdate(sql, params)
//			}else if(params instanceof Map){
////				sqlClient.execute(params, sql)
//				sqlClient.executeUpdate(params, sql)
//			}
//		}else{
//			sqlClient.executeUpdate(sql)
//		}
//	}

	protected Field getDomainField(String columnName, Class<?> domainClass){
		return getDomainField(columnName, domainClass, false);
	}
	
	/**
	 * 
	 * @param columnName
	 * @param domainClass
	 * @return
	 */
	protected Field getDomainField(String columnName, Class<?> domainClass, boolean alias){
		Field target = null;
		
		DomainClassMetaData domainClassMetaData = JdbcDataFactory.createDomainClassMetaData(domainClass);
		
		List<DomainFieldMetaData> list = domainClassMetaData.getDomainFieldMetaDatas();
		
		for(DomainFieldMetaData meta : list){
			Field field = meta.getField();
			
			if(!alias){
				String plainName = DBConverter.unescapeColumnName(meta.getColumnName());
				if(columnName.equalsIgnoreCase(plainName) || columnName.equals(DomainUtils.convertToDomainFieldName(plainName))){
					target = field;
					break;
				}
			}else{
				String aliasFieldName = DomainUtils.convertToDomainFieldName(columnName);
				if(aliasFieldName.equals(field.getName())){
					target = field;
					break;
				}
			}
		}
		
		return target;
	}
	
	/**
	 * 
	 * @param domain
	 * @return
	 * @throws Exception
	 */
	protected IdField getIdField(Object domain) throws Exception {
		DomainClassMetaData domainClassMetaData = JdbcDataFactory.createDomainClassMetaData(domain.getClass());
		
		Optional<DomainFieldMetaData> optional = domainClassMetaData.getDomainFieldMetaDatas().stream().filter(m -> m.isIdKey()).findFirst();
		
		IdField field = null;
		
		if(optional.isPresent()){
			DomainFieldMetaData meta = optional.get();
			String idFieldName = meta.getField().getName();
		
			field = new IdField();
			field.setFieldName(idFieldName);
			field.setValue(GroovyUtils.getProperty(domain, idFieldName));
			field.setColumnName(meta.getColumnName());
		}
		
		return field;
	}
	
	/**
	 * 
	 * @param value
	 * @param field
	 * @return
	 */
	protected Object formatFieldValue(Object value, Field field){
		Object nvalue = value;
		
		PersistFormat persistFormat = field.getAnnotation(PersistFormat.class);
		
		if(persistFormat != null && (value instanceof String)){
			PersistCase persistCase = persistFormat.value();
			if(PersistCase.Upper == persistCase){
				nvalue = DataUtils.convertToString(value).toUpperCase();
			}else if(PersistCase.Lower == persistCase){
				nvalue = DataUtils.convertToString(value).toLowerCase();
			}
		}

		return nvalue;
	}
	
	protected void logSql(GString sql){
		log.info("execute sql -> {}", sql);
	}
	
	protected void logSql(String sql){
		log.info("execute sql -> {}", sql);
	}
	
	protected void logSql(String sql, Object params){
		logSql(sql);
		if(params instanceof Collection || params instanceof Object[]){
			log.debug("sql params -> {}", toList(params));
		}else{
			log.debug("sql params -> {}", params);
		}
	}

	public Sql getSqlClient(){
		return sqlClient;
	}
	
	/**
	 * 
	 * @return
	 */
	public DataSource getDataSource() {
		return dataSource;
	}

	public Connection getConnection() {
		return connection;
	}
}
